﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PRC_TB_ALERTS_CLEAN_REFERENCES')
	BEGIN
		DROP  Procedure  dbo.PRC_TB_ALERTS_CLEAN_REFERENCES
	END

GO

CREATE Procedure dbo.PRC_TB_ALERTS_CLEAN_REFERENCES
	(
		@TA_ID int
	)

AS

begin transaction

	delete TB_ALERTS_NOTIFICATION_TYPE where TNT_ALERT_ID = @TA_ID

	-- Delete all tasks alert references if the alert is a task alert.
	declare @TAT_ID int
	set @TAT_ID = (select tat_id from TB_ALERTS_TASKS where TAT_ALERT_ID = @TA_ID)
	if @TAT_ID is not null
	begin
		delete TB_ALERTS_TASKS_CATEGORIES where TATC_ALERT_TASK_ID = @TAT_ID
		delete TB_ALERTS_TASKS_PROJECTS where TATP_ALERT_TASK_ID = @TAT_ID
		delete TB_ALERTS_TASKS_STATES where TATS_ALERT_TASK_ID = @TAT_ID
		delete TB_ALERTS_TASKS_USERS where TATU_ALERT_TASK_ID = @TAT_ID

		delete TB_ALERTS_TASKS_OVERDUE where TATO_ALERT_TASK_ID = @TAT_ID
		delete TB_ALERTS_TASKS_HISTORY_EXCED_TASK where TATH_ALERT_TASK_ID = @TAT_ID

		delete TB_ALERTS_TASKS where TAT_ID = @TAT_ID
	end

if @@error <> 0
	begin
		rollback
	end
else
	begin
		commit
	end

GO

GRANT EXEC ON dbo.PRC_TB_ALERTS_CLEAN_REFERENCES TO PUBLIC

GO

